package com.atguigu.edu.realtime.publisher.mapping;

import com.atguigu.edu.realtime.publisher.bean.SourceTraPer;
import com.atguigu.edu.realtime.publisher.bean.TradeCt;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TradeCountMapper {
    @Select("  select source_name name, sum(order_uu_count) uCt, sum(order_count) orderCt, sum(final_amount) orderAmount\n" +
            "  from dws_trade_subject_chapter_course_province_source_order_window\n" +
            "  where toYYYYMMDD(stt) = #{date}\n" +
            "  group by source_name ")
    List<TradeCt> selectSourceOrderCnt(Integer date);

    @Select("  select province_name name, sum(order_uu_count) uCt, sum(order_count) orderCt, sum(final_amount) orderAmount\n" +
            "  from dws_trade_subject_chapter_course_province_source_order_window\n" +
            "  where toYYYYMMDD(stt) = #{date}\n" +
            "  group by province_name ")
    List<TradeCt> selectProvinceOrderCnt(Integer date);

    @Select("  select 'all' name, sum(order_uu_count) uCt, sum(order_count) orderCt, sum(final_amount) orderAmount\n" +
            "  from dws_trade_subject_chapter_course_province_source_order_window\n" +
            "  where toYYYYMMDD(stt) = #{date}\n" +
            "  group by province_name ")
    List<TradeCt> selectOrderCnt(Integer date);

    @Select("select source_name, order_uu_count/uu_ct traPer\n" +
            "from\n" +
            "(select\n" +
            "  source_name,sum(uu_ct) uu_ct\n" +
            "from\n" +
            "dws_user_user_login_window \n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by source_name\n" +
            ")t1\n" +
            "left join  \n" +
            "( \n" +
            "  select source_name,sum(order_uu_count) order_uu_count\n" +
            "  from\n" +
            "  dws_trade_subject_chapter_course_province_source_order_window\n" +
            "   where toYYYYMMDD(stt) = #{date}\n" +
            "  group by source_name\n" +
            ") t2 \n" +
            "on t1.source_name = t2.source_name")
    List<SourceTraPer> selectSourceTraPer(Integer date);
}
